<?php
/**
 * Created by PhpStorm.
 * User: wei
 * Date: 2016/6/2
 * Time: 11:10
 */
namespace Admin\Controller;
use Think\Controller;

class RateManageController extends CommonController
{
    protected function _initialize(){
        parent::_initialize();
        $this->breadcrumb1='财务管理';
        $this->breadcrumb2='师傅维保金利息管理';
        $this->url=U('RateManage/index');
        $this->sqlname='ratemanage'.session('user_auth')['uid'];
        $this->dsqlname='ratemanagedetail'.session('user_auth')['uid'];
    }

    public function index(){
        $phone=I('post.phone');
        $name=I('post.name');

        if($phone){
            $where['u.phone']=array('like',"%$phone%");
        }
        if($name){
            $where['ifo.id_name']=array('like',"%$name%");
        }

        //查询数据
        $where['ro.pay_time']=array('gt',0);
        $count=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_ratemanage r on r.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->where($where)
            ->count();

        $Page       = new \Think\Page($count,25);
        //记录sql
        $sql=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_ratemanage r on r.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->where($where)
            ->field('u.phone,ifo.id_name,p.bond,(select sum(money) from zsf_ratemanage where user_id=ro.worker_user_id) money,u.user_id')
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select(false);

        $redis = S(array('type' => 'redis'));
        S($this->sqlname, $sql);
        $list=M()->query($sql);

        $total=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_ratemanage r on r.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->where($where)
            ->field('sum(money) total')
            ->find();
     /*   $list=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_id')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->field('u.user_id,u.phone,ifo.id_name,p.bond,(select sum(money) from zsf_ratemanage where user_id=ro.worker_user_id) money')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();*/

       // $total=M('ratemanage')->field('sum(money) total')->find();

        $show       = $Page->show();
        $this->assign('list',$list);
        $this->assign('total',$total['total']);
        $this->assign('page',$show);// 赋值分页输出
        $this->display();

    }


    //详情页da
    public function detail(){
        $id=I('get.id');
        $date1=I('get.date1');
        $date2=I('get.date2');
        $rate_id=I('get.rate_id');
        if($date1 && $date2){
            $time1=strtotime($date1);
            $time2=strtotime($date2);
            $where['r.add_time']=array('gt',$time1);
            $where['r.add_time']=array('lt',$time2);
        }

        if($rate_id){
            $where['r.id']=$rate_id;
        }

        $where['u.user_id']=$id;
        $count=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->where($where)
            ->count();

        $Page       = new \Think\Page($count,5);
        $list=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->field('u.phone,ifo.id_name,p.bond,r.money,r.id,r.add_time')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select();
        //记录sql
        $sql=M('repair_order ro')->join('left join zsf_user_real_info ifo on ro.worker_user_id=ifo.user_id')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_user u on u.user_id=ro.worker_user_id')
            ->join('left join zsf_ratemanage r on r.user_id=u.user_id')
            ->field('u.phone,ifo.id_name,p.bond,r.money,r.id,r.add_time')
            ->where($where)
            ->limit($Page->firstRow.','.$Page->listRows)
            ->select(false);
        $redis = S(array('type' => 'redis'));
        S($this->dsqlname,$sql);

        $total=M('repair_order ro')
            ->join('left join zsf_repair_order_protocol p on p.order_id=ro.repair_order_num_alias')
            ->join('left join zsf_ratemanage r on r.user_id=ro.worker_user_id')
            ->where('ro.pay_time>0')
            ->field('sum(p.bond) b_total,sum(r.money) m_total')
            ->select();

        $show       = $Page->show();
        $this->assign('detail',$list);
        $this->assign('total',$total);
        $this->assign('page',$show);// 赋值分页输出
        $this->display();
    }



    //导出
    public function excelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->sqlname);
        //设置列名称
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'师傅账号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'师傅名称','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'保证金总额(元)','size'=>'20'),
            'E'=>array('index'=>'E1','name'=>'总利息(元)','size'=>'20'),
        );
        //设置标题
        $title='师傅维保金利息管理';
        //选择类型
        $type=5;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }

    //利息导出
    public function rateexcelout(){
        $redis = S(array('type' => 'redis'));
        $sql=S($this->dsqlname);
        //设置列名称7
        $sheetindex=array(
            'A'=>array('index'=>'A1','name'=>'序号','size'=>'20'),
            'B'=>array('index'=>'B1','name'=>'利息编号','size'=>'20'),
            'C'=>array('index'=>'C1','name'=>'保证金(元)','size'=>'20'),
            'D'=>array('index'=>'D1','name'=>'利息(元)','size'=>'20'),
            'E'=>array('index'=>'E1','name'=>'产生时间','size'=>'20'),
        );
        //设置标题
        $title='师傅利息详情';
        //选择类型
        $type=6;
        //执行sql
        $data = M()->query($sql);
        phpExcel($sheetindex,$data,$title,$type);
        exit;
    }
}